package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/tealeg/xlsx"
	"html/template"
	"os"
	"strconv"
	"strings"
	"sync"
	_ "time"
)

//列
type columns struct {
	excelColumns []*xlsx.Cell
	tableColumns []string
	useColumns   [][]string
}

//解析Excel及数据库字段
func (c *columns) paraseColumns() {
	c.useColumns = make([][]string, len(c.excelColumns))
	for key, value := range c.excelColumns {
		thiscolumn := value.String()
		columnval := strings.Split(thiscolumn, "|")
		//判断是否为合法字段
		for _, val := range c.tableColumns {
			if columnval[0] == val {
				c.useColumns[key] = columnval
			}
		}
	}
	if len(c.useColumns) < 1 {
		fmt.Println("数据表字段与xlsx表格映射字段不对应")
		os.Exit(-1)
	}
}

//行
type row struct {
	insertID int64
	sql      string
	value    map[string]string
	ot       otherTable
}

//关联表
type otherTable struct {
	sql     string
	value   []string
	columns []string
}

//任务信息
type worker struct {
	id int
	//唯一
	uniquecell  int
	uniquename  string
	tablename   string
	rowsnum     int
	chquequenum int
	c           *columns
	xlFile      *xlsx.File
	db          *sql.DB

	in   chan int
	done func()
}

func isUniqurGoodid(keyname string, keyvalue string, tableName string, db *sql.DB) bool {
	result, _ := fetchRow(db, "SELECT count("+keyname+") as has FROM `"+tableName+"` WHERE `"+keyname+"` = '"+keyvalue+"'")
	unhas, _ := strconv.Atoi((*result)["has"])
	if unhas > 0 {
		return false
	}
	return true
}
func parsekey(funcname string, value string, params string) string {
	switch funcname {
	case "replace":
		fmt.Println("过滤")
		fmt.Println(funcname)
		fmt.Println(value)

		val := strings.Replace(value, params, "", -1)
		val = strings.Replace(val, " ", "", -1)

		fmt.Println(val)
		return val
		break
	default:
		return value
	}

	return value
}

/// 执行任务
func dowork(id int, work worker) {
	for n := range work.in {
		for m := n*work.chquequenum + 1; m <= (n+1)*work.chquequenum; m++ {
			if m >= work.rowsnum {
				break
			}

			unhas := true

			if work.uniquecell != 999 {
				///避免上传重复值
				goodid := work.xlFile.Sheets[0].Rows[m].Cells[work.uniquecell].String()
				unhas = isUniqurGoodid(work.uniquename, goodid, work.tablename, work.db)
			}

			if unhas {
				tmp := 0
				r := &row{value: make(map[string]string), sql: "INSERT INTO `" + work.tablename + "` SET ", ot: otherTable{}}
				for key, value := range work.c.useColumns {
					//fmt.Printf("useColums m is %d, key is %d, value is %s \n", m, key, value[0])
					if work.xlFile.Sheets[0].Rows[m].Cells[key].String() != "" {
						/// 判断是否需要处理 goodname|replace:%
						keyname := value[0]
						keyvalue := paraseValue(work.xlFile.Sheets[0].Rows[m].Cells[key].String())

						if len(value) >= 2 {
							if strings.Contains(value[1], ":") {
								funccallinfo := strings.Split(value[1], ":")
								keyvalue = parsekey(funccallinfo[0], keyvalue, funccallinfo[1])
							}
						}

						if work.xlFile.Sheets[0].Rows[m].Cells[key].String() == "nil" {
							r.value[keyname] = ""
						} else {
							r.value[keyname] = keyvalue
						}
						if tmp == 0 {
							r.sql += "`" + keyname + "` = '" + template.HTMLEscapeString(r.value[keyname]) + "'"
						} else {
							r.sql += ", `" + keyname + "` = '" + template.HTMLEscapeString(r.value[keyname]) + "'"
						}
						tmp++
					}
				}
				tx, err := work.db.Begin()
				defer tx.Rollback()
				checkerr(err)
				smt, err := tx.Prepare(r.sql + ";")
				checkerr(err)
				res, err := smt.Exec()
				if err != nil {
					fmt.Println("sql is error")
					fmt.Println(r.sql + ";")
				}
				r.insertID, err = res.LastInsertId()
				if err != nil {
					fmt.Println("insret is fail")
					fmt.Println(r.sql + ";")
					checkerr(err)
				}
				err = tx.Commit()
				smt.Close()
				fmt.Printf("workerID: %d, received: %d, excleID %d, rowID %d \n", id, n, m, r.insertID)
			}
		}
		work.done()
	}
}

/// 创建任务
func createWork(uniquecell int, wg *sync.WaitGroup, id int, uniquename string, rowsnum int, chquequenum int, tablename string, c *columns, xlFile *xlsx.File, db *sql.DB) worker {
	workerparam := worker{
		uniquecell:  uniquecell,
		uniquename:  uniquename,
		rowsnum:     rowsnum,
		chquequenum: chquequenum,
		tablename:   tablename,
		c:           c,
		xlFile:      xlFile,
		db:          db,
		in:          make(chan int),
		done: func() {
			wg.Done()
		}}
	go dowork(id, workerparam)
	return workerparam
}

/// Usage: /Users/kevin/Desktop/Go/src/execltomysql/execltomysql
// /Users/kevin/Downloads/taoke0425.xlsx
// root:pwd@tcp\(ip:3306\)/tablename?charset=utf8
// tablename
// key
// 0

func main() {

	if len(os.Args) < 4 {
		fmt.Println("工具使用方式： excel表格路径 数据库连接dsn 指定数据表名tablename")
		os.Exit(-1)
	}

	//获取参数信息
	excelpath := os.Args[1]
	mysqldsn := os.Args[2]
	tableName := os.Args[3]

	uniquename := os.Args[4]
	uniquecell := os.Args[5]

	fmt.Printf("excel path: %s, mysql dsn: %s, table name: %s \n", excelpath, mysqldsn, tableName)

	//打开mysql数据库
	//获取数据表所有字段
	db := getDbInstance(mysqldsn)
	defer db.Close()

	cellcolums := new(columns)

	rows, err := db.Query("SELECT * FROM " + tableName + " LIMIT 1")
	checkerr(err)

	cellcolums.tableColumns, err = rows.Columns()
	checkerr(err)
	rows.Close()

	/// 表格字段数
	//for key, value := range cellcolums.tableColumns{
	//	fmt.Printf("key is %d, columnname is %s \n", key, value)
	//}

	//打开xlsx文件
	xlFile, err := xlsx.OpenFile(excelpath)
	checkerr(err)

	cellcolums.excelColumns = xlFile.Sheets[0].Rows[0].Cells
	cellcolums.paraseColumns()

	//行数
	rowsnum := len(xlFile.Sheets[0].Rows)

	//每个队列执行数量
	chquequenum := 400

	//开启的协程数量
	chnums := ((rowsnum - 1) / chquequenum) + 1

	fmt.Printf("上传记录数 %d, 分配协程数 %d \n", (rowsnum - 1), chnums)

	//启动任务
	channels := make([]worker, chnums)
	wg := sync.WaitGroup{}
	wg.Add(chnums)

	uni, err := strconv.Atoi(uniquecell)
	checkerr(err)

	for i := 0; i < chnums; i++ {
		channels[i] = createWork(uni, &wg, i, uniquename, rowsnum, chquequenum, tableName, cellcolums, xlFile, db)
	}

	for i, worker := range channels {
		worker.in <- i
	}

	wg.Wait()
}

//解析内容
func paraseValue(val string) string {
	switch val {
	case ":null":
		return ""
	default:
		return val
	}
}
